﻿//Copyrigh (C) 2021 上海东欣软件工程有限公司 版权所有。
//<author>邢耀光</author>
//<data>2021.1.31</data>

using System;
using System.Collections.Generic;
using System.Web.Mvc;
using System.Data;
using Newtonsoft.Json;
using Service;
using Core;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Collections.Concurrent;
using System.Threading.Tasks;

namespace Mvc5DemoWeb.Controllers
{
    /// <summary>
    /// 树结构定义类(EXTJS-4.2中 树结构所需最基本的元素)
    /// </summary>
    public class TreeNode
    {
        //节点ID
        public int ID { get; set; }
        //是否叶子节点
        public bool Leaf { get; set; }
        //节点文本名称
        public string Text { get; set; }
        //子节点集合
        public List<TreeNode> Children { get; set; }
    }
    /// <summary>
    /// HomeController控制层操作
    /// </summary>
    public class HomeController : Controller
    {
        #region Index
        /// <summary>
        /// Index主页界面
        /// </summary>
        /// <returns>界面信息</returns>
        public ActionResult Index()
        {
            return View();
        }
        #endregion

        #region About
        /// <summary>
        /// About界面
        /// </summary>
        /// <returns>界面信息</returns>
        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }
        #endregion

        #region Contact
        /// <summary>
        /// Contact界面
        /// </summary>
        /// <returns>界面信息</returns>
        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
        #endregion

        #region GetAllDepartmentData
        /// <summary>
        /// 根据id获得的部门下一级节点信息
        /// </summary>
        ///  <param name="id">部门id</param>
        /// <returns>json字符串</returns>
        public ActionResult GetAllDepartmentDataByID(int id)
        {
            //数据库连接字符串
            string connString = this.GetSqlConnection();
            //部门信息服务类
            DepartmentService departmentService = new DepartmentService();            
            List<TreeNode> nodes = new List<TreeNode>();
            // 获得部门节点下级部门节点
            List<Department> departments = departmentService.GetDepartmentChildrens(connString, id);
            foreach (var item in departments)
            {
                TreeNode subNode = new TreeNode();
                subNode.ID = item.ID;
                subNode.Text = item.Name;
                subNode.Children = new List<TreeNode>();
                if (departmentService.ExistDepartmentChildren(connString, item.ID))
                {
                    subNode.Leaf = false;
                    TreeNode subNode1 = new TreeNode();
                    subNode1.ID = 999;
                    subNode1.Text = "假节点";
                    subNode1.Leaf = true;
                    subNode1.Children = new List<TreeNode>();
                    subNode.Children.Add(subNode1);
                }
                else
                {
                    subNode.Leaf = true;
                }
                nodes.Add(subNode);
            }
            string resultData = JsonConvert.SerializeObject(nodes);
            //(EXTJS-4.2中 树结构所需的最基本的元素为小写，其中小写字母是固定的，不能随意更改)
            //树结构完整元素请查阅Extjs文档
            resultData = resultData.Replace("ID", "id");
            resultData = resultData.Replace("Text", "text");
            resultData = resultData.Replace("Leaf", "leaf");
            resultData = resultData.Replace("Children", "children");
            return Content(resultData);
        }
        #endregion

        #region GetAllDepartmentData
        /// <summary>
        /// 初始化获得的部门树信息
        /// </summary>
        /// <returns>json字符串</returns>
        public ActionResult GetAllDepartmentData()
        {
            //数据库连接字符串
            string connString = this.GetSqlConnection();
            //部门信息服务类
            DepartmentService departmentService = new DepartmentService();
            List<TreeNode> nodes = new List<TreeNode>();
            //获得部门根节点    
            Department department = departmentService.GetDepartmentRoot(connString);
            TreeNode node = new TreeNode();
            //首次加载树，需加载根及以下节点
            node.ID = department.ID;
            node.Leaf = false;
            node.Text = department.Name;
            node.Children = new List<TreeNode>();
            // 获得部门根节点下级部门节点
            List<Department> departments = departmentService.GetDepartmentChildrens(connString, node.ID);
            foreach (var item in departments)
            {
                TreeNode subNode = new TreeNode();
                subNode.ID = item.ID;
                subNode.Text = item.Name;
                subNode.Children = new List<TreeNode>();
                if (departmentService.ExistDepartmentChildren(connString, item.ID))
                {
                    //因为前台树结构，每次展开，只展开下一层节点，所以下一层如果还有节点，则加载一个假节点
                    //如果继续展开下一层节点，则重新加载下一层节点数据，以此类推。
                    subNode.Leaf = false;
                    TreeNode subNode1 = new TreeNode();
                    subNode1.ID = 999;
                    subNode1.Text = "假节点";
                    subNode1.Leaf = true;
                    subNode1.Children = new List<TreeNode>();
                    subNode.Children.Add(subNode1);
                    node.Children.Add(subNode);
                }
                else
                {
                    subNode.Leaf = true;
                    node.Children.Add(subNode);
                }
            }
            nodes.Add(node);
            string resultData = JsonConvert.SerializeObject(nodes);
            //(EXTJS-4.2中 树结构所需的最基本的元素为小写，其中小写字母是固定的，不能随意更改)
            //树结构完整元素请查阅Extjs文档
            resultData = resultData.Replace("ID", "id");
            resultData = resultData.Replace("Text", "text");
            resultData = resultData.Replace("Leaf", "leaf");
            resultData = resultData.Replace("Children", "children");
            return Content(resultData);
        }
        #endregion

        #region GetALLUserData
        /// <summary>
        /// 获得所有的用户信息
        /// </summary>
        /// <returns>json字符串</returns>
        public ActionResult GetAllUserData(string userName, string userDesc, int userDepID)
        {
            //数据库连接字符串
            string connString = this.GetSqlConnection();
            //用户信息服务类
            UserService userService = new UserService();
            //部门信息服务类
             DepartmentService departmentService = new DepartmentService();
            //用户查询参数
            User args = new User();
            args.Name = userName;
            args.Description = userDesc;
            args.DepartmentID = userDepID;
            //查询
            //每页大小
            int pageSize = Int32.Parse(Request.QueryString["limit"]);
            //当前页
            int page = Int32.Parse(Request.QueryString["page"]);
            int totalRows;
            System.Data.DataTable users = userService.QueryUserPages(args, connString, pageSize, page, out totalRows);
            PaingData<System.Data.DataTable> userPaingData = new PaingData<System.Data.DataTable>();
            userPaingData.Value = users;
            userPaingData.Page = page;
            userPaingData.TotalRows = totalRows;
            string resultData = JsonConvert.SerializeObject(userPaingData);
            return Content(resultData);
        }
        #endregion

        #region PaingData<T>
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <typeparam name="T">值类型</typeparam>
        public class PaingData<T>{
            //值数据
            public T Value { get; set; }
            //获得页索引
            public int Page { get; set; }
            //获得总页数
            public int TotalPages { get; set; }
            //获得总行树
            public int TotalRows { get; set; }

        }
        #endregion

        #region CreateUserData
        /// <summary>
        /// 创建用户信息
        /// </summary>
        /// <param name="userName">用户名称</param>
        /// <param name="userDesc">描述</param>
        /// <param name="userDepID">部门ID</param>
        /// <returns>执行结果</returns>
        public ActionResult CreateUserData(string userName, string userDesc, int userDepID)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //用户信息服务类
                UserService userService = new UserService();
                if (userService.ExistUser(connString, userName, userDepID))
                {
                    result = "{success:false,msg:'该部门用户已经存在！'}";
                }
                else {
                    //用户信息
                    User user = new User();
                    user.Name = userName;
                    user.Description = userDesc;
                    user.DepartmentID = userDepID;
                    //是否添加成功
                    bool isCreateSucess = userService.InsertUser(user, connString);
                    //成功
                    if (isCreateSucess)
                    {
                        result = "{success:true,msg:'添加成功！'}";
                    }
                }
            }                
            catch (Exception ex)
            {
                result = "{success:false,msg:'" + ex.Message + "'}";
            }

            return Content(result);
        }
        #endregion

        #region UpdateUserData
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="userName">用户名称</param>
        /// <param name="userDesc">描述</param>
        /// <param name="userDepID">部门ID</param>
        /// <returns>执行结果</returns>
        public ActionResult UpdateUserData(int userId, string userName, string userDesc, int userDepID)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //用户信息服务类
                UserService userService = new UserService();
                if (userService.ExistUser(connString, userName, userDepID))
                {
                    User args = new User();
                    args.Name = userName;
                    List<User> users = userService.ExactQueryUser(args, connString);
                    if (users.Count == 1 && users[0].ID == userId)
                    {
                        //用户信息
                        User user = new User();
                        user.ID = userId;
                        user.Name = userName;
                        user.Description = userDesc;
                        user.DepartmentID = userDepID;
                        //是否修改成功
                        bool isUpdateSucess = userService.UpdateUser(user, connString);
                        if (isUpdateSucess)
                        {
                            result = "{success:true,msg:'修改成功！'}";
                        }
                    }
                    else
                    {
                        result = "{success:false,msg:'修改失败：该部门用户已经存在！'}";
                    }
                }
                else
                {
                    //用户信息
                    User user = new User();
                    user.ID = userId;
                    user.Name = userName;
                    user.Description = userDesc;
                    user.DepartmentID = userDepID;
                    //是否修改成功
                    bool isUpdateSucess = userService.UpdateUser(user, connString);
                    if (isUpdateSucess)
                    {
                        result = "{success:true,msg:'修改成功！'}";
                    }
                }               
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'" + ex.Message + "'}";
            }
            return Content(result);
        }
        #endregion

        #region DeleteUserData
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns>执行结果</returns>
        public ActionResult DeleteUserData(int userId)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //用户信息服务类
                UserService userService = new UserService();
                //是否修改成功
                bool isDeleteSucess = userService.DeleteUser(userId, connString);
                if (isDeleteSucess)
                {
                    result = "{success:true,msg:'删除成功！'}";
                }
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'删除失败：" + ex.Message + "'}";
            }
            return Content(result);
        }
        #endregion

        #region SearchUserData
        /// <summary>
        /// 查询用户信息
        /// </summary>
        /// <param name="userName">名称</param>
        /// <param name="userDesc">描述</param>
        /// <param name="userDepID">部门ID</param>
        /// <returns>执行结果</returns>
        public ActionResult SearchUserData(string userName, string userDesc, int userDepID)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //用户信息服务类
                UserService userService = new UserService();                
                //用户查询参数
                User args = new User();
                args.Name = userName;
                args.Description = userDesc;
                args.DepartmentID = userDepID;
                //每页大小
                int pageSize = Int32.Parse("20");
                //当前页
                int page = Int32.Parse("1");
                int totalRows;
                System.Data.DataTable users = userService.QueryUserPages(args, connString, pageSize, page, out totalRows);
                PaingData<System.Data.DataTable> userPaingData = new PaingData<System.Data.DataTable>();
                userPaingData.Value = users;
                userPaingData.Page = page;
                userPaingData.TotalPages = totalRows % pageSize == 0 ? totalRows / pageSize : totalRows / pageSize + 1;
                userPaingData.TotalRows = totalRows;
                string resultData = JsonConvert.SerializeObject(userPaingData);
                result = "{success:true,msg:'" + resultData + "'}";
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'" + ex.Message + "'}";
            }
            return Content(result);
        }
        #endregion

        #region CreateDepartmentData
        /// <summary>
        /// 创建部门信息
        /// </summary>
        /// <param name="parentID">父部门id</param>
        /// <param name="name">部门名称</param>
        /// <returns>执行结果</returns>
        public ActionResult CreateDepartmentData(int parentID, string name)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //部门信息服务类
                DepartmentService departmentService = new DepartmentService();
                if (departmentService.ExistDepartment(connString, name))
                {
                    result = "{success:false,msg:'添加失败：该部门已经存在！'}";
                }
                else
                {
                    //部门信息
                    Department department = new Department();
                    department.ParentID = parentID;
                    department.Name = name;
                    //是否添加成功
                    bool isCreateSucess = departmentService.InsertDepartment(department, connString);
                    //成功
                    if (isCreateSucess)
                    {
                        result = "{success:true,msg:'添加成功！'}";
                    }
                }                
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'添加失败：" + ex.Message + "'}";
            }

            return Content(result);
        }
        #endregion

        #region UpdateDepartmentData
        /// <summary>
        /// 修改部门信息
        /// </summary>
        /// <param name="id">部门ID</param>
        /// <param name="parentID">部门父ID</param>
        /// <param name="name">部门名称</param>
        /// <returns>执行结果</returns>
        public ActionResult UpdateDepartmentData(int id, int parentID, string name)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //部门服务类
                DepartmentService departmentService = new DepartmentService();
                if (departmentService.ExistDepartment(connString, name))
                {
                    Department args = new Department();
                    args.Name = name;
                    List<Department> departments=departmentService.ExactQueryDepartment(args, connString);
                    if (departments.Count == 1 && departments[0].ID == id)
                    {
                        //部门信息
                        Department department = new Department();
                        department.ID = id;
                        department.ParentID = parentID;
                        department.Name = name;
                        //是否修改成功
                        bool isUpdateSucess = departmentService.UpdateDepartment(department, connString);
                        if (isUpdateSucess)
                        {
                            result = "{success:true,msg:'修改成功！'}";
                        }
                    }
                    else {
                        result = "{success:false,msg:'修改失败：该部门已经存在！'}";
                    }                    
                }
                else
                {
                    //部门信息
                    Department department = new Department();
                    department.ID = id;
                    department.ParentID = parentID;
                    department.Name = name;
                    //是否修改成功
                    bool isUpdateSucess = departmentService.UpdateDepartment(department, connString);
                    if (isUpdateSucess)
                    {
                        result = "{success:true,msg:'修改成功！'}";
                    }
                }
               
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'" + ex.Message + "'}";
            }
            return Content(result);
        }
        #endregion

        #region DeleteDepartmentData
        /// <summary>
        /// 删除部门信息
        /// </summary>
        /// <param name="id">部门ID</param>
        /// <returns>执行结果</returns>
        public ActionResult DeleteDepartmentData(int id)
        {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //部门信息服务类
                DepartmentService departmentService = new DepartmentService();
                //是否修改成功
                bool isDeleteSucess = departmentService.DeleteDepartment(id, connString);
                if (isDeleteSucess)
                {
                    result = "{success:true,msg:'删除成功！'}";
                }
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'删除失败：" + ex.Message + "'}";
            }
            return Content(result);
        }
        #endregion
        
        #region GetSqlConnection
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns>数据库连接字符串</returns>
        private string GetSqlConnection()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["SPDM"].ToString();

            return connString;
        }
        #endregion

        #region ImportExcel
        /// <summary>
        /// 读取EXCEL
        /// <param name="importDepartmentID">部门ID</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public ActionResult ImportExcel(int departmentID)
        {            
            System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("Excel");
            foreach (System.Diagnostics.Process p in process)
            {
                if (!string.IsNullOrEmpty(p.ProcessName))
                {
                    try
                    {
                        p.Kill();
                    }
                    catch { }
                }
            }
            string result = string.Empty;
            bool success = false;
            Application excel = null;
            Workbooks wbs = null;
            Workbook wb = null;
            Worksheet ws = null;
            Range range1 = null;
            try
            {             

                excel = new Application();
                excel.UserControl = true;
                excel.DisplayAlerts = false;

                object Nothing = Missing.Value;
                string filePath = "D:/导入部门用户数据缓存文件.xlsx";
                //另存为
                Request.Files[0].SaveAs(filePath);
                excel.Application.Workbooks.Open(filePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
                wbs = excel.Workbooks;
                wb = wbs[1];
                ws = (Worksheet)wb.Worksheets[1];
                int columnCount = ws.UsedRange.Cells.Columns.Count;
                int rowCount = ws.UsedRange.Cells.Rows.Count;
                             
                range1 = ws.get_Range((Range)ws.Cells[2, 1], (Range)ws.Cells[rowCount, columnCount]);
                object[,] obj = (object[,])range1.Value2;
                range1.Clear();
                System.Data.DataTable table = new System.Data.DataTable();
                // 添加表头                
                table.Columns.Add("Name");
                table.Columns.Add("Description");
                table.Columns.Add("DepartmentID");
                table.Columns.Add("IsSystemOnly");
                table.Columns.Add("UserType");
                table.Columns.Add("Creator");
                table.Columns.Add("CreateTime");
                table.Columns.Add("Modifier");
                table.Columns.Add("ModifyTime");
                for (int row = 1; row <= obj.GetLength(0); row++)
                {
                    table.Rows.Add(GetArray(obj, row, table, departmentID));
                    if (row % 500 == 0 || row == obj.GetLength(0))
                    {
                        System.Data.DataTable queueDT = table.Copy();
                        queue.Enqueue(queueDT); //模拟数据入队
                        table.Clear();
                    }
                }
                //用户信息服务类
                UserService userService = new UserService();
                //部门信息服务类
                DepartmentService departmentService = new DepartmentService();
                System.Data.DataTable dtepartment = departmentService.GetDescendantDepartments(this.GetSqlConnection(), departmentID);
                List<int> departmentIds = new List<int>();
                departmentIds.Add(departmentID);
                foreach (DataRow dr in dtepartment.Rows)
                {
                    departmentIds.Add(Convert.ToInt32(dr["ID"]));
                }
                foreach (var item in departmentIds)
                {
                    userService.DeleteUserByDepartment(item,this.GetSqlConnection());
                }
                //int threadCount = 2; //开启5个线程
                //for (int i = 0; i < threadCount; i++)
                //{
                //    //开启新线程
                //    Task.Factory.StartNew(() =>
                //    {
                //数据循环出队
                while (queue.TryDequeue(out System.Data.DataTable model))
                {
                    ////处理数据
                    //if (model != null)
                    //    sb.AppendLine($"==》Code={model.Code},Msg={model.Msg},Data={model.Data}");
                    success = userService.ImportUser(this.GetSqlConnection(), model);
                }
                //    });
                //}
                //success = userService.ImportUser(this.GetSqlConnection(), table);
                wb.Close();
                // 关闭相关变量
                wb = null;
                excel = null;
                wbs = null;
                if (success) {
                    result = "{success:true,msg:'报表导入成功'}";
                } else {
                    result = "{success:false,msg:'报表导入失败'}";
                }
                
            }
             catch (Exception ex)
            {
                wb.Close();
                // 关闭相关变量
                wb = null;
                excel = null;
                wbs = null;
                result = "{success:false,msg:'" + ex.Message + "'}";
            }

            return Content(result);
        }

        //线程安全队列
        private ConcurrentQueue<System.Data.DataTable> queue = new ConcurrentQueue<System.Data.DataTable>();
        private DataRow GetArray(object[,] obj, int row, System.Data.DataTable table, int departmentID) {

            DataRow newDataRow = table.NewRow();
            newDataRow["Name"] = obj[row, 1] == null ? "" : obj[row, 1].ToString();
            newDataRow["Description"] = obj[row, 2] == null ? "" : obj[row, 2].ToString();
            newDataRow["DepartmentID"] = departmentID;
            newDataRow["IsSystemOnly"] = obj[row, 5] == null ? false : Convert.ToBoolean(obj[row, 5]);
            newDataRow["UserType"] = obj[row, 4]==null ? 0 : Convert.ToByte(obj[row, 4]);
            newDataRow["Creator"] = obj[row, 6] == null ? "" : obj[row, 6].ToString();            
            newDataRow["CreateTime"] = obj[row, 7] == null ? DateTime.MinValue : DateTime.FromOADate(Convert.ToInt32(obj[row, 7]));
            newDataRow["Modifier"] = obj[row, 8] == null ? "" : obj[row, 8].ToString();
            newDataRow["ModifyTime"] = obj[row, 9] == null ? DateTime.MinValue : DateTime.FromOADate(Convert.ToInt32(obj[row, 9]));
            return newDataRow;
        }
        #endregion

        #region ImportExcel
        /// <summary>
        /// 导出报表
        /// </summary>
        /// <param name="departmentID">部门id</param>
        /// <returns></returns>
        public ActionResult ExportData(int departmentID ) {
            string result = string.Empty;
            try
            {
                //数据库连接字符串
                string connString = this.GetSqlConnection();
                //用户信息服务类
                UserService userService = new UserService();
                DepartmentService departmentService = new DepartmentService();
                System.Data.DataTable dts = new System.Data.DataTable();
                Department argsDepartment = new Department();
                argsDepartment.ID = departmentID;
                string departmentName = departmentService.QueryDepartment(argsDepartment, connString)[0].Name;
                System.Data.DataTable dtepartment = departmentService.GetDescendantDepartments(connString, departmentID);
                List<int> departmentIds = new List<int>();
                departmentIds.Add(departmentID);
                foreach (DataRow dr in dtepartment.Rows)
                {
                    departmentIds.Add(Convert.ToInt32(dr["ID"]));
                }
                foreach (var id in departmentIds)
                {
                    //用户查询参数
                    User args = new User();                    
                    args.DepartmentID = id;
                    //查询
                    System.Data.DataTable dtUser = userService.QueryUserDT(args, connString);
                    dts.Merge(dtUser,true);                    
                }
                dts.Columns.Remove("ID");
                dts.Columns.Remove("DepartmentID");
                ExportExcel(dts,departmentName);
                result = "{success:true,msg:'报表生成成功'}";
            }
            catch (Exception ex)
            {
                result = "{success:false,msg:'" + ex.Message + "'}";
            }
            return Content(result);
        }

        /// <summary>
        /// //Excel导出的时候有两种软件插件可以使用（一种是office一种wps），因为各个插件的dll使用的方法不一样，因此要判断用户安装了哪个软件。
        /// </summary>
        /// <param name="dt">数据</param>
        private void ExportExcel(System.Data.DataTable dt, string departmentName)
        {

            //个人做的是政府项目，讲究国产化，在这里我先判断用户是否安装了wps。
            Type type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型
            if (type == null){//"检测到您的电脑上没有安装office或WSP软件，请先安装！";
                throw new Exception("检测到您的电脑上没有安装office");
            }
            OfficeExcel(type, dt, departmentName);
        }
        /// <summary>
        /// 安装了office表格生成
        /// </summary>
        /// <param name="type">类型</param>
        /// <param name="dt">数据</param>
        private void OfficeExcel(Type type, System.Data.DataTable dt, string departmentName)
        {
            //新建一个应用程序EXCL
            Application myExcel = new Application();
            //打开并显示EXCEL文件
            Workbook workbookData = myExcel.Workbooks.Add(Missing.Value);
            //获取Excel文件中的第一张工作表
            Worksheet xlSheet = (Worksheet)workbookData.Worksheets[1];
            //工作表名称
            xlSheet.Name = departmentName;
            //工作表标题
            xlSheet.Cells[1, 1] = "用户";
            xlSheet.Cells[1, 2] = "描述";
            xlSheet.Cells[1, 3] = "部门";
            xlSheet.Cells[1, 4] = "用户类型";
            xlSheet.Cells[1, 5] = "是否系统";
            xlSheet.Cells[1, 6] = "创建者";
            xlSheet.Cells[1, 7] = "创建时间";
            xlSheet.Cells[1, 8] = "修改者";
            xlSheet.Cells[1, 9] = "修改时间";
            //全表自动列宽
            xlSheet.Cells.Columns.AutoFit();
            //设置选中颜色
            xlSheet.get_Range((Range)xlSheet.Cells[1, 1], (Range)xlSheet.Cells[1, 9]).Select();
            //xlSheet.get_Range((Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[1, 9]).Interior.ColorIndex = 1;

            Range range;
            try
            {
                range = xlSheet.get_Range("A1", Missing.Value);
                range = range.get_Resize(1, 9);
                range.Font.Bold = true;
                range.Font.Size = 12;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.ColumnWidth = "16";
                range.RowHeight = "16";
                range.WrapText = true;
                range.EntireRow.AutoFit();

                range = xlSheet.get_Range("A2", Missing.Value);
                range = range.get_Resize(dt.Rows.Count, dt.Columns.Count);
                range.Font.Bold = false;
                range.Font.Size = 10;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.ColumnWidth = "16";
                range.RowHeight = "16";
                range.WrapText = false;
                range.EntireRow.AutoFit();

                object[,] saRet = new object[dt.Rows.Count, dt.Columns.Count];
                for (int iRow = 0; iRow < dt.Rows.Count; iRow++)
                {
                    for (int iCol = 0; iCol < dt.Columns.Count; iCol++)
                    {
                        saRet[iRow, iCol] = dt.Rows[iRow][iCol];
                    }
                }
                range.set_Value(Missing.Value, saRet);
                workbookData.SaveAs("./myexcel.xlsx");
                //设置EXCL打开后可见
                myExcel.Visible = true;
                myExcel.UserControl = true;

            }
            catch (Exception)
            {
                throw;
            }
        }
     }
    #endregion
}
